程序员练级攻略：数据库-[2026重制版]

-- PostgreSQL 17 JSON 示例
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

-- 插入JSON数据
INSERT INTO products (name, attributes)
VALUES ('iPhone', '{"color": "black", "storage": "256GB", "price": 999}');

-- JSONPath 查询 (PG 17)
SELECT name, attributes->>'color' as color,
       attributes->'price'::numeric as price
FROM products
WHERE attributes @> '{"storage": "256GB"}';

-- 创建GIN索引加速JSON查询
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- MySQL 9.0 JavaScript存储程序示例
CREATE FUNCTION js_add(a INT, b INT)
RETURNS INT
LANGUAGE JAVASCRIPT AS $$
    return a + b;
$$;

-- Vector 类型示例 (用于AI/ML)
CREATE TABLE embeddings (
    id INT PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)  -- OpenAI维度
);

-- 向量相似度搜索
SELECT content, COSINE_DISTANCE(embedding, '[0.1,0.2,...]') as distance
FROM embeddings
ORDER BY distance
LIMIT 10;

-- 联合索引示例（订单系统）
CREATE INDEX idx_orders_user_status_time
ON orders(user_id, status, created_at);

-- 这个查询会命中索引
SELECT * FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC;

-- 但这个不会（缺少user_id）
SELECT * FROM orders WHERE status = 'paid';  -- 全表扫描！

# Redis 7 Functions 示例（Lua脚本升级版）
# 注册一个函数
redis.execute_command('FUNCTION', 'LOAD', '#!lua name=mylib\n'
    'redis.register_function("myfunc", function(keys, args)\n'
    '    return redis.call("incr", keys[1])\n'
    'end)')

# 调用函数
result = redis.fcall("myfunc", 1, "mycounter")

// MongoDB 8.0 时序集合示例
db.createCollection(
    "temperature",
    {
        timeseries: {
            timeField: "timestamp",
            metaField: "sensorId",
            granularity: "hours"
        }
    }
);

// 自动按时间聚合
db.temperature.insertMany([
    { timestamp: new Date(), sensorId: "A", value: 25.3 },
    { timestamp: new Date(), sensorId: "B", value: 26.1 }
]);

// 时序查询（自动压缩）
db.temperature.aggregate([
    { $match: { sensorId: "A", timestamp: { $gte: ISODate("2024-01-01") } } },
    { $group: {
        _id: { $dateTrunc: { date: "$timestamp", unit: "day" } },
        avgValue: { $avg: "$value" }
    }}
]);